package com.gymmsystem.GYMMSystem.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.gymmsystem.GYMMSystem.entity.Course;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import java.util.List;
import java.util.Map;

@Mapper
public interface CourseMapper extends BaseMapper<Course> {
    // 根据课程ID获取课程详情（动态计算状态）
    @Select("SELECT c_id, c_t_id, c_name, c_grade, c_introduction, c_image, c_time, c_duration, " +
            "c_max, c_current, c_status, c_created_at, " +
            "CASE " +
            "  WHEN c_time > NOW() THEN '即将开始' " +
            "  WHEN NOW() BETWEEN c_time AND DATE_ADD(c_time, INTERVAL c_duration MINUTE) THEN '进行中' " +
            "  ELSE '已结束' " +
            "END as c_run_status " +
            "FROM courses WHERE c_id = #{courseId}")
    Course selectByIdWithRunStatus(@Param("courseId") Long courseId);

    // 获取可预约课程列表（动态计算状态）
    @Select("SELECT c_id, c_t_id, c_name, c_grade, c_introduction, c_image, c_time, c_duration, " +
            "c_max, c_current, c_status, c_created_at, " +
            "CASE " +
            "  WHEN c_time > NOW() THEN '即将开始' " +
            "  WHEN NOW() BETWEEN c_time AND DATE_ADD(c_time, INTERVAL c_duration MINUTE) THEN '进行中' " +
            "  ELSE '已结束' " +
            "END as c_run_status " +
            "FROM courses WHERE c_status = 1 AND c_time > NOW() ORDER BY c_time ASC")
    List<Course> findAvailableCourses();

    // 根据教练ID查询课程（动态计算状态）
    @Select("SELECT c_id, c_t_id, c_name, c_grade, c_introduction, c_image, c_time, c_duration, " +
            "c_max, c_current, c_status, c_created_at, " +
            "CASE " +
            "  WHEN c_time > NOW() THEN '即将开始' " +
            "  WHEN NOW() BETWEEN c_time AND DATE_ADD(c_time, INTERVAL c_duration MINUTE) THEN '进行中' " +
            "  ELSE '已结束' " +
            "END as c_run_status " +
            "FROM courses WHERE c_t_id = #{coachId} AND c_status = 1 ORDER BY c_time DESC")
    List<Course> findCoursesByCoachId(@Param("coachId") Long coachId);

    @Select("SELECT COUNT(*) FROM courses WHERE c_t_id = #{coachId} AND c_time = #{courseTime} AND c_status = 1")
    int countCoachCoursesAtTime(@Param("coachId") Long coachId, @Param("courseTime") String courseTime);

    @Update("UPDATE courses SET c_current = c_current + 1 WHERE c_id = #{courseId} AND c_current < c_max")
    int incrementCurrentCount(@Param("courseId") Long courseId);

    @Update("UPDATE courses SET c_current = c_current - 1 WHERE c_id = #{courseId} AND c_current > 0")
    int decrementCurrentCount(@Param("courseId") Long courseId);

    // 获取包含教练信息的课程列表（动态计算状态）
    @Select("SELECT c.c_id, c.c_t_id, c.c_name, c.c_grade, c.c_introduction, c.c_image, c.c_time, c.c_duration, " +
            "c.c_max, c.c_current, c.c_status, c.c_created_at, " +
            "co.name as coach_name, co.phone as coach_phone, co.specialty as coach_specialty, " +
            "CASE " +
            "  WHEN c.c_time > NOW() THEN '即将开始' " +
            "  WHEN NOW() BETWEEN c.c_time AND DATE_ADD(c.c_time, INTERVAL c.c_duration MINUTE) THEN '进行中' " +
            "  ELSE '已结束' " +
            "END as c_run_status " +
            "FROM courses c " +
            "LEFT JOIN coaches co ON c.c_t_id = co.id " +
            "WHERE c.c_status = 1 AND c.c_time > NOW() " +
            "ORDER BY c.c_time ASC")
    List<Course> findCoursesWithCoachInfo();

    @Select("SELECT c_max, c_current FROM courses WHERE c_id = #{courseId} AND c_status = 1")
    Map<String, Object> getCourseCapacity(@Param("courseId") Long courseId);

    // 新增：更新课程运行状态
    @Update("UPDATE courses SET c_run_status = #{status} WHERE c_id = #{courseId}")
    int updateRunStatus(@Param("courseId") Long courseId, @Param("status") String status);

    // 新增：根据课程ID获取图片URL
    @Select("SELECT c_image FROM courses WHERE c_id = #{courseId}")
    String findCourseImageById(@Param("courseId") Long courseId);

    // 新增：更新课程图片
    @Update("UPDATE courses SET c_image = #{imageUrl} WHERE c_id = #{courseId}")
    int updateCourseImage(@Param("courseId") Long courseId, @Param("imageUrl") String imageUrl);
    
    // 更新课程状态（上架/下架）
    @Update("UPDATE courses SET c_status = #{status} WHERE c_id = #{courseId}")
    int updateCourseStatus(@Param("courseId") Long courseId, @Param("status") Integer status);

    /**
     * 更新指定课程的预约状态
     * @param courseId 课程ID
     * @param fromStatus 原状态
     * @param toStatus 目标状态
     * @return 更新的记录数
     */
    @Update("UPDATE reservations SET r_status = #{toStatus}, r_updated_at = NOW() " +
            "WHERE r_c_id = #{courseId} AND r_status = #{fromStatus}")
    int updateReservationStatusForCourse(@Param("courseId") Long courseId,
                                         @Param("fromStatus") String fromStatus,
                                         @Param("toStatus") String toStatus);
    
    // 分页查询课程列表（管理员使用）
    @Select("SELECT c.c_id as cId, c.c_name as cName, c.c_grade as cGrade, c.c_time as cTime, " +
            "c.c_duration as cDuration, c.c_max as cMax, c.c_current as cCurrent, c.c_status as cStatus, " +
            "c.c_introduction as cIntroduction, c.c_image as cImage, c.c_t_id as cTId, " +
            "co.name as coach_name, " +
            "CASE " +
            "  WHEN c.c_time > NOW() THEN '即将开始' " +
            "  WHEN NOW() BETWEEN c.c_time AND DATE_ADD(c.c_time, INTERVAL c.c_duration MINUTE) THEN '进行中' " +
            "  ELSE '已结束' " +
            "END as c_run_status " +
            "FROM courses c " +
            "LEFT JOIN coaches co ON c.c_t_id = co.id " +
            "WHERE c.c_status = #{status} " +
            "ORDER BY c.c_created_at DESC " +
            "LIMIT #{offset}, #{limit}")
    List<Map<String, Object>> findCoursesByPageAndStatus(@Param("offset") Integer offset, @Param("limit") Integer limit, @Param("status") Integer status);
    
    // 根据状态获取课程总数（管理员使用）
    @Select("SELECT COUNT(*) FROM courses WHERE c_status = #{status}")
    Integer getCourseTotalCountByStatus(@Param("status") Integer status);
    
    // 分页查询课程列表（带状态和搜索）
    @Select("SELECT c.c_id as cId, c.c_name as cName, c.c_grade as cGrade, c.c_time as cTime, " +
            "c.c_duration as cDuration, c.c_max as cMax, c.c_current as cCurrent, c.c_status as cStatus, " +
            "c.c_introduction as cIntroduction, c.c_image as cImage, c.c_t_id as cTId, " +
            "co.name as coach_name, " +
            "CASE " +
            "  WHEN c.c_time > NOW() THEN '即将开始' " +
            "  WHEN NOW() BETWEEN c.c_time AND DATE_ADD(c.c_time, INTERVAL c.c_duration MINUTE) THEN '进行中' " +
            "  ELSE '已结束' " +
            "END as c_run_status " +
            "FROM courses c " +
            "LEFT JOIN coaches co ON c.c_t_id = co.id " +
            "WHERE c.c_status = #{status} " +
            "AND (c.c_name LIKE CONCAT('%', #{search}, '%') " +
            "OR co.name LIKE CONCAT('%', #{search}, '%') " +
            "OR c.c_grade LIKE CONCAT('%', #{search}, '%')) " +
            "ORDER BY c.c_created_at DESC " +
            "LIMIT #{offset}, #{limit}")
    List<Map<String, Object>> findCoursesByPageStatusAndSearch(@Param("offset") Integer offset, @Param("limit") Integer limit, @Param("status") Integer status, @Param("search") String search);
    
    // 根据状态和搜索关键词获取课程总数
    @Select("SELECT COUNT(*) FROM courses c " +
            "LEFT JOIN coaches co ON c.c_t_id = co.id " +
            "WHERE c.c_status = #{status} " +
            "AND (c.c_name LIKE CONCAT('%', #{search}, '%') " +
            "OR co.name LIKE CONCAT('%', #{search}, '%') " +
            "OR c.c_grade LIKE CONCAT('%', #{search}, '%'))")
    Integer getCourseTotalCountByStatusAndSearch(@Param("status") Integer status, @Param("search") String search);
    
    // 原有分页查询方法保留（兼容旧调用）
    @Select("SELECT c.c_id as cId, c.c_name as cName, c.c_grade as cGrade, c.c_time as cTime, " +
            "c.c_duration as cDuration, c.c_max as cMax, c.c_current as cCurrent, c.c_status as cStatus, " +
            "c.c_introduction as cIntroduction, c.c_image as cImage, c.c_t_id as cTId, " +
            "co.name as coach_name, " +
            "CASE " +
            "  WHEN c.c_time > NOW() THEN '即将开始' " +
            "  WHEN NOW() BETWEEN c.c_time AND DATE_ADD(c.c_time, INTERVAL c.c_duration MINUTE) THEN '进行中' " +
            "  ELSE '已结束' " +
            "END as c_run_status " +
            "FROM courses c " +
            "LEFT JOIN coaches co ON c.c_t_id = co.id " +
            "ORDER BY c.c_created_at DESC " +
            "LIMIT #{offset}, #{limit}")
    List<Map<String, Object>> findCoursesByPage(@Param("offset") Integer offset, @Param("limit") Integer limit);
    
    // 原有获取课程总数方法保留（兼容旧调用）
    @Select("SELECT COUNT(*) FROM courses")
    Integer getCourseTotalCount();
    
    // 获取所有课程（管理员使用）
    @Select("SELECT c.c_id as cId, c.c_name as cName, c.c_grade as cGrade, c.c_time as cTime, " +
            "c.c_duration as cDuration, c.c_max as cMax, c.c_current as cCurrent, c.c_status as cStatus, " +
            "c.c_introduction as cIntroduction, c.c_image as cImage, c.c_t_id as cTId, " +
            "co.name as coach_name, " +
            "CASE " +
            "  WHEN c.c_time > NOW() THEN '即将开始' " +
            "  WHEN NOW() BETWEEN c.c_time AND DATE_ADD(c.c_time, INTERVAL c.c_duration MINUTE) THEN '进行中' " +
            "  ELSE '已结束' " +
            "END as c_run_status " +
            "FROM courses c " +
            "LEFT JOIN coaches co ON c.c_t_id = co.id " +
            "ORDER BY c.c_created_at DESC")
    List<Map<String, Object>> findAllCourses();

    // 在CourseMapper接口中添加方法
    @Select("SELECT co.id, co.name, co.phone, co.specialty, co.experience, co.image_url " +
            "FROM courses c " +
            "LEFT JOIN coaches co ON c.c_t_id = co.id " +
            "WHERE c.c_id = #{courseId}")
    Map<String, Object> findCoachInfoByCourseId(@Param("courseId") Long courseId);
}